package mappers;

import handler.DatabaseConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Club;
import model.Match;
import model.Player;

public class PlayerMapper {

    private static PlayerMapper instance;
    private ArrayList<Player> players;

    private PlayerMapper() {
        players = getTable();
    }

    public static PlayerMapper getInstance() {
        if (instance == null) {
            instance = new PlayerMapper();
        }
        return instance;
    }

    public ArrayList<Player> getTable() {
        players = new ArrayList<>();
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            String SQL = "Select * from player";
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                int id = rs.getInt("playerid");
                String fName = rs.getString("firstname");
                String lName = rs.getString("lastname");
                int pNumber = rs.getInt("playernumber");
                String pPosition = rs.getString("position");

                //Henter klubben som spilleren hører til.
                int clubid = rs.getInt("club");
                Club club = ClubMapper.getInstance().getClub(clubid);

                players.add(new Player(id, fName, lName, pNumber, pPosition, club));
            }

            stmt.close();
            rs.close();
        } catch (SQLException ex) {
            System.out.println("SQL FEJL: " + ex.getMessage());
        }
        return players;
    }

    public ArrayList<Player> getPlayers() {
        if (players == null) {
            getTable();
        }
        return players;
    }

    public ArrayList<Player> getPlayers(Match match) {
        ArrayList<Player> selectedPlayers = new ArrayList<>();
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            stmt.execute("drop table if exists selected_players");
            String SQL = "create table selected_players ";
            SQL += "as (select * from player,game where club = hometeam and gameid =" + match.getMatchId() + ")";
            SQL += "union (select * from player,game where club = awayteam and gameid =" + match.getMatchId() + ")";
            stmt.execute(SQL);
            SQL = "select playerid,firstname,lastname,playernumber,position,club from ";
            SQL += "selected_players";
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {
                int id = rs.getInt("playerid");
                String fName = rs.getString("firstname");
                String lName = rs.getString("lastname");
                int pNumber = rs.getInt("playernumber");
                String pPosition = rs.getString("position");

                //Henter klubben som spilleren hører til.
                int clubid = rs.getInt("club");
                Club club = ClubMapper.getInstance().getClub(clubid);

                selectedPlayers.add(new Player(id, fName, lName, pNumber, pPosition, club));
            }

            stmt.close();
            rs.close();
        } catch (SQLException ex) {
            System.out.println("SQL FEJL: " + ex.getMessage());
        }
        
        return selectedPlayers;
    }

    // Henter en player ud fra id.
    public Player getPlayer(int id) {
        //Må godt være null, det ændres uanset hvad, hvis ikke er det en fejl.
        Player player = null;
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            String SQL = "select * from player where playerid = " + id;
            ResultSet rs = stmt.executeQuery(SQL);

            rs.next();
            int playerId = rs.getInt("playerid");
            String fName = rs.getString("firstname");
            String lName = rs.getString("lastname");
            int pNumber = rs.getInt("playernumber");
            String pPosition = rs.getString("position");

            //Henter klubben som spilleren hører til.
            int clubid = rs.getInt("club");
            Club club = ClubMapper.getInstance().getClub(clubid);

            player = new Player(playerId, fName, lName, pNumber, pPosition, club);

            stmt.close();
            rs.close();
        } catch (SQLException ex) {
            System.out.println("SQL FEJL: " + ex.getMessage());
        }
        return player;
    }
}
